package cn.bisoft.java.service.dao;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import com.hengpeng.common.dao.EntityConfig;
import com.hengpeng.common.dao.SQLUtil;
import com.hengpeng.common.exception.DataOperationException;

public abstract class BaseDao extends JdbcDaoSupport
{
	private final static Map<String, EntityConfig> entityMap = SQLUtil.getEntityMap();

	public BaseDao()
	{
		super();
	}

	protected void deleteAllEntity(Collection<?> entitys) throws DataOperationException
	{
		try
		{
			List<String> list = new ArrayList<String>();
			for (Iterator<?> iter = entitys.iterator(); iter.hasNext();)
			{
				Object element = (Object) iter.next();
				EntityConfig dc = (EntityConfig) BaseDao.entityMap.get(element.getClass().getName());

				String sql = SQLUtil.createStatement(dc, element, SQLUtil.ENTITY_DELETE);
				list.add(sql);
			}
			this.executeBatchSql(list);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	protected void deleteEntity(Object entity) throws DataOperationException
	{
		try
		{
			EntityConfig dc = (EntityConfig) BaseDao.entityMap.get(entity.getClass().getName());
			String sql = SQLUtil.createStatement(dc, entity, SQLUtil.ENTITY_DELETE);
			this.getJdbcTemplate().execute(sql);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	protected int[] executeBatchSql(List<String> list) throws DataOperationException
	{
		int[] result = new int[list.size()];
		try
		{
			if (list.size() > 0)
			{
				if (list.size() > 5000)
				{
					int loopCount = list.size() / 5000;
					for (int i = 0; i < loopCount; i++)
					{
						List<String> subList = list.subList(i * 5000, (i + 1) * 5000);
						String[] sqlArray = subList.toArray(new String[0]);
						int[] temp = this.getJdbcTemplate().batchUpdate(sqlArray);
						for (int j = 0; j < loopCount; j++)
						{
							result[i * 5000 + j] = temp[j];
						}
					}

					if (list.size() % 5000 != 0)
					{
						List<String> subList = list.subList(loopCount * 5000, list.size());
						String[] sqlArray = subList.toArray(new String[0]);
						int[] temp = this.getJdbcTemplate().batchUpdate(sqlArray);
						for (int j = 0; j < loopCount; j++)
						{
							result[loopCount * 5000 + j] = temp[j];
						}
					}
				}
				else
				{
					String[] sqlArray = list.toArray(new String[0]);
					result = this.getJdbcTemplate().batchUpdate(sqlArray);
				}
			}
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
		return result;
	}

	/**
	 * 批量执行原生SQL语句
	 * @param sql 原生SQL语句
	 * @return an array of the number of rows affected by each statement
	 * @exception DataOperationException 数据库操作异常
	 */
	protected int[] executeBatchSql(String[] sql) throws DataOperationException
	{
		try
		{
			return this.executeBatchSql(Arrays.asList(sql));
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * 执行存储过程语句
	 * @param sql 存储过程调用SQL语句
	 * @param values 传入参数值数组, 只接受String, Integer, Date, Double数据类型, 其它都转换为String
	 * @exception DataOperationException 数据库操作异常
	 */
	@SuppressWarnings("unchecked")
	protected List<List<Object>> executeSPList(String sql, final Object[] values) throws DataOperationException
	{
		try
		{
			CallableStatementCallback action = new CallableStatementCallback()
			{
				public List<List<Object>> doInCallableStatement(CallableStatement cs) throws SQLException,
						DataAccessException
				{
					for (int i = 0; i < values.length; i++)
					{
						Object object = values[i];
						if (Double.class.isInstance(object))
						{
							cs.setDouble(i + 1, (Double) object);
						}
						else if (Integer.class.isInstance(object))
						{
							cs.setInt(i + 1, (Integer) object);
						}
						else if (Date.class.isInstance(object))
						{
							cs.setTimestamp(i + 1, new Timestamp(((Date) object).getTime()));
						}
						else
						{
							cs.setString(i + 1, (String) object);
						}
					}
					return SQLUtil.rs2List(cs.executeQuery());
				}
			};
			return (List<List<Object>>) this.getJdbcTemplate().execute(sql, action);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * 执行原生SQL语句
	 * @param sql 原生SQL语句
	 * @exception DataOperationException 数据库操作异常
	 */
	protected void executeSql(String sql) throws DataOperationException
	{
		try
		{
			this.getJdbcTemplate().execute(sql);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * 执行原生SQL语句
	 * @param sql 原生SQL语句
	 * @exception DataOperationException 数据库操作异常
	 */
	protected int executeUpdate(String sql) throws DataOperationException
	{
		try
		{
			return this.getJdbcTemplate().update(sql);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * 执行存储过程语句
	 * @param sql 存储过程调用SQL语句
	 * @param action 回调函数
	 * @exception DataOperationException 数据库操作异常
	 */
	protected Object executeStoredProcedure(String sql, CallableStatementCallback action) throws DataOperationException
	{
		try
		{
			return this.getJdbcTemplate().execute(sql, action);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * <pre>
	 * 通过实体类型和查询语句获取实体对象集合
	 * </pre>
	 * @param entityClass 实体类型
	 * @param sql 查询语句
	 * @return 实体对象集合
	 * @throws DataOperationException 数据库处理异常
	 */
	protected <T> List<T> findEntityBySql(Class<T> entityClass, String sql) throws DataOperationException
	{
		try
		{
			SqlRowSet rowset = this.getJdbcTemplate().queryForRowSet(sql);
			return SQLUtil.SqlRowSet2List(rowset, entityClass);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * <pre>
	 * 通过实体类型和主键获取实体对象
	 * </pre>
	 * @param entityClass 实体类型
	 * @param entityId 主键值
	 * @return 实体对象，未找到则返回null
	 * @throws DataOperationException 数据库处理异常
	 */
	protected <T> T getEntity(Class<T> cl, String entityId) throws DataOperationException
	{
		try
		{
			EntityConfig dc = (EntityConfig) BaseDao.entityMap.get(cl.getName());
			String sql = SQLUtil.createStatement(dc, cl, entityId, SQLUtil.ENTITY_SELECT);
			SqlRowSet rowset = this.getJdbcTemplate().queryForRowSet(sql);
			List<T> list = SQLUtil.SqlRowSet2List(rowset, cl);
			if (list.size() > 0)
			{
				return list.get(0);
			}
			else
			{
				return null;
			}
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * <pre>
	 * 通过实体类型和主键获取实体对象
	 * </pre>
	 * @param entityClass 实体类型
	 * @param entityId 主键值
	 * @return 实体对象，未找到则返回null
	 * @throws DataOperationException 数据库处理异常
	 */
	protected <T> T lockEntity(Class<T> cl, String entityId) throws DataOperationException
	{
		try
		{
			EntityConfig dc = (EntityConfig) BaseDao.entityMap.get(cl.getName());
			String sql = SQLUtil.createStatement(dc, cl, entityId, SQLUtil.ENTITY_SELECT);
			SqlRowSet rowset = this.getJdbcTemplate().queryForRowSet(sql);
			List<T> list = SQLUtil.SqlRowSet2List(rowset, cl);
			if (list.size() > 0)
			{
				return list.get(0);
			}
			else
			{
				return null;
			}
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * <pre>
	 * 通过实体类型，条件语句获得记录数
	 * </pre>
	 * @param entityClass 实体类型
	 * @param filter 条件语句，前面不加WHERE
	 * @return 记录数
	 * @throws DataOperationException 数据库操作异常
	 */
	protected int getRowCount(Class<?> entityClass, String filter) throws DataOperationException
	{
		try
		{
			EntityConfig dc = (EntityConfig) BaseDao.entityMap.get(entityClass.getName());
			String sql = SQLUtil.createCountStatement(dc, filter);
			return this.getJdbcTemplate().queryForInt(sql);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * 通过查询SQL获得记录数
	 * @param sql 查询语句，格式为：SELECT COUNT(id) FROM table WHERE...
	 * @return 记录数
	 * @throws DataOperationException 数据库操作异常
	 */
	protected int getRowCount(String sql) throws DataOperationException
	{
		try
		{
			return this.getJdbcTemplate().queryForInt(sql);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * 通过查询SQL获得某个字段值的对象
	 * @param sql 查询语句，格式为：SELECT id FROM table WHERE...
	 * @param requiredType 返回的数据类型,例如java.lang.String
	 * @return 字段值的对象
	 * @throws DataOperationException 数据库操作异常
	 */
	@SuppressWarnings("unchecked")
	protected <T> T getSingleObject(String sql, Class<T> requiredType) throws DataOperationException
	{
		try
		{
			return (T) this.getJdbcTemplate().queryForObject(sql, requiredType);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * <pre>
	 * 通过实体类型和主键获取实体对象
	 * </pre>
	 * @param entityClass 实体类型
	 * @param entityId 主键值
	 * @return 实体对象，未找到则抛异常
	 * @throws DataOperationException 数据库处理异常
	 */
	protected <T> T loadEntity(Class<T> entityClass, String entityId) throws DataOperationException
	{
		try
		{
			EntityConfig dc = (EntityConfig) BaseDao.entityMap.get(entityClass.getName());
			String sql = SQLUtil.createStatement(dc, entityClass, entityId, SQLUtil.ENTITY_SELECT);
			SqlRowSet rowset = this.getJdbcTemplate().queryForRowSet(sql);
			List<T> list = SQLUtil.SqlRowSet2List(rowset, entityClass);
			if (list.size() > 0)
			{
				return list.get(0);
			}
			else
			{
				throw new DataOperationException("未找到记录.");
			}
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * <pre>
	 * 通过实体类型和条件语句分页获取实体对象集合
	 * 注意：此方法以主键排倒序。
	 * </pre>
	 * @param entityClass 实体类型,必须为已经在entity-config.xml定义的实体类型
	 * @param filter 条件语句，前面不加WHERE
	 * @param orderFieldName 排序字段
	 * @param isOrderAsc 是否升序排序
	 * @param pageSize 每页显示的记录数
	 * @param pageIndex 当前页数
	 * @return 实体对象集合
	 * @throws DataOperationException 数据库处理异常
	 */
	protected <T> List<T> paginationQueryFast(Class<T> entityClass, String filter, int pageSize, int pageIndex)
			throws DataOperationException
	{
		try
		{
			EntityConfig dc = (EntityConfig) BaseDao.entityMap.get(entityClass.getName());
			return this.paginationQueryNormal(entityClass, filter, dc.getPrimaryKey(), false, pageSize, pageIndex);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * <pre>
	 * 通过实体类型和条件语句分页获取实体对象集合, 重载此方法目的是为了能够方便查询视图.
	 * 注意：此方法以主键排倒序。
	 * </pre>
	 * @param entityClass 实体类型
	 * @param tableName 表名或者视图名
	 * @param primaryKey 主键名称
	 * @param filter 条件语句，前面不加WHERE
	 * @param orderFieldName 排序字段
	 * @param isOrderAsc 是否升序排序
	 * @param pageSize 每页显示的记录数
	 * @param pageIndex 当前页数
	 * @return 实体对象集合
	 * @throws DataOperationException 数据库处理异常
	 */
	protected <T> List<T> paginationQueryFast(Class<T> entityClass, String tableName, String primaryKey, String filter,
			int pageSize, int pageIndex) throws DataOperationException
	{
		return this.paginationQueryNormal(entityClass, tableName, primaryKey, filter, primaryKey, false, pageSize,
				pageIndex);
	}

	/**
	 * <pre>
	 * 通过实体类型和条件语句分页获取实体对象集合
	 * </pre>
	 * @param entityClass 实体类型,必须为已经在entity-config.xml定义的实体类型
	 * @param filter 条件语句，前面不加WHERE
	 * @param orderFieldName 排序字段
	 * @param isOrderAsc 是否升序排序
	 * @param pageSize 每页显示的记录数
	 * @param pageIndex 当前页数
	 * @return 实体对象集合
	 * @throws DataOperationException 数据库处理异常
	 */
	protected <T> List<T> paginationQueryNormal(Class<T> entityClass, String filter, String orderFieldName,
			boolean isOrderAsc, int pageSize, int pageIndex) throws DataOperationException
	{
		try
		{
			EntityConfig dc = (EntityConfig) BaseDao.entityMap.get(entityClass.getName());
			String sql = SQLUtil.assemblePaginationSQLByEntity(dc, filter, orderFieldName, isOrderAsc, pageSize,
					pageIndex);
			return this.findEntityBySql(entityClass, sql);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * <pre>
	 * 通过实体类型和条件语句分页获取实体对象集合, 重载此方法目的是为了能够方便查询视图.
	 * </pre>
	 * @param entityClass 实体类型
	 * @param tableName 表名或者视图名
	 * @param primaryKey 主键名称
	 * @param filter 条件语句，前面不加WHERE
	 * @param orderFieldName 排序字段
	 * @param isOrderAsc 是否升序排序
	 * @param pageSize 每页显示的记录数
	 * @param pageIndex 当前页数
	 * @return 实体对象集合
	 * @throws DataOperationException 数据库处理异常
	 */
	protected <T> List<T> paginationQueryNormal(Class<T> entityClass, String tableName, String primaryKey,
			String filter, String orderFieldName, boolean isOrderAsc, int pageSize, int pageIndex)
			throws DataOperationException
	{
		try
		{
			String sql = SQLUtil.assemblePaginationSQL(tableName, primaryKey, "*", filter, orderFieldName, isOrderAsc,
					pageSize, pageIndex);
			return this.findEntityBySql(entityClass, sql);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * <pre>
	 * 更新实体对象到持久化数据
	 * </pre>
	 * @param entity 实体对象
	 * @throws DataOperationException 数据库处理异常
	 */
	protected void update(Object entity) throws DataOperationException
	{
		try
		{
			EntityConfig dc = (EntityConfig) BaseDao.entityMap.get(entity.getClass().getName());
			String sql = SQLUtil.createStatement(dc, entity, SQLUtil.ENTITY_UPDATE);
			this.getJdbcTemplate().execute(sql);
		}
		catch (RuntimeException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * <pre>
	 * 更新实体对象集合到持久化数据
	 * </pre>
	 * @param entitys 实体对象集合
	 * @throws DataOperationException 数据库处理异常
	 */
	protected void updateAll(Collection<?> entitys) throws DataOperationException
	{
		try
		{
			List<String> list = new ArrayList<String>();
			for (Iterator<?> iter = entitys.iterator(); iter.hasNext();)
			{
				Object element = (Object) iter.next();
				EntityConfig dc = (EntityConfig) BaseDao.entityMap.get(element.getClass().getName());

				String sql = SQLUtil.createStatement(dc, element, SQLUtil.ENTITY_UPDATE);
				list.add(sql);
			}
			this.executeBatchSql(list);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * <pre>
	 * 更新实体对象到持久化数据
	 * </pre>
	 * @param entity 实体对象
	 * @throws DataOperationException 数据库处理异常
	 */
	protected void saveOrUpdate(Object entity) throws DataOperationException
	{
		try
		{
			EntityConfig dc = (EntityConfig) BaseDao.entityMap.get(entity.getClass().getName());
			String sql = SQLUtil.createStatement(dc, entity, SQLUtil.ENTITY_SAVE_OR_UPDATE);
			this.getJdbcTemplate().execute(sql);
		}
		catch (RuntimeException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * <pre>
	 * 更新实体对象集合到持久化数据
	 * </pre>
	 * @param entitys 实体对象集合
	 * @throws DataOperationException 数据库处理异常
	 */
	protected void saveOrUpdateAll(Collection<?> entitys) throws DataOperationException
	{
		try
		{
			List<String> list = new ArrayList<String>();
			for (Iterator<?> iter = entitys.iterator(); iter.hasNext();)
			{
				Object element = (Object) iter.next();
				EntityConfig dc = (EntityConfig) BaseDao.entityMap.get(element.getClass().getName());

				String sql = SQLUtil.createStatement(dc, element, SQLUtil.ENTITY_SAVE_OR_UPDATE);
				list.add(sql);
			}
			this.executeBatchSql(list);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * <pre>
	 * 保存实体对象到持久化数据
	 * </pre>
	 * @param entity 实体对象
	 * @throws DataOperationException 数据库处理异常
	 */
	protected void saveEntity(Object entity) throws DataOperationException
	{
		try
		{
			EntityConfig dc = (EntityConfig) BaseDao.entityMap.get(entity.getClass().getName());
			String sql = SQLUtil.createStatement(dc, entity, SQLUtil.ENTITY_INSERT);
			this.getJdbcTemplate().execute(sql);
		}
		catch (RuntimeException e)
		{
			throw new DataOperationException(e);
		}
	}

	/**
	 * <pre>
	 * 保存实体对象集合到持久化数据
	 * </pre>
	 * @param entitys 实体对象集合
	 * @throws DataOperationException 数据库处理异常
	 */
	protected void saveAllEntity(Collection<?> entitys) throws DataOperationException
	{
		try
		{
			List<String> list = new ArrayList<String>();
			for (Iterator<?> iter = entitys.iterator(); iter.hasNext();)
			{
				Object element = (Object) iter.next();
				EntityConfig dc = (EntityConfig) BaseDao.entityMap.get(element.getClass().getName());

				String sql = SQLUtil.createStatement(dc, element, SQLUtil.ENTITY_INSERT);
				list.add(sql);
			}
			this.executeBatchSql(list);
		}
		catch (DataAccessException e)
		{
			throw new DataOperationException(e);
		}
	}
}
